import csv
import pymysql
import threading

host = 'localhost'
user = 'root'
passwd = '123456'
db = 'spider'
class DateProcess():
    #连接数据库
    def connect(self):
        conn=pymysql.connect(host=host,
                               user=user,
                               passwd=passwd,
                               db=db,
                               charset="utf8")
        return conn

    #创建数据库
    def create(self,conn):
        cur = conn.cursor()
        cur.execute("DROP TABLE IF EXISTS `recruitment`;")
        sql = """CREATE TABLE `recruitment`  (
                      `id` int(11) NOT NULL AUTO_INCREMENT,
                      `Web` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
                      `Url` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
                      `Job` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
                      `MaxPay` decimal(10, 0) NULL DEFAULT NULL,
                      `MinPay` decimal(10, 0) NULL DEFAULT NULL,
                      `Education` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
                      `Experience` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
                      `City` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
                      `Company` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
                      `CompanyType` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
                      PRIMARY KEY (`id`) USING BTREE
                    ) ;"""
        cur.execute(sql)
        cur.close()
        #获取全部数据
    '''获取所有数据'''
    def selectAll(self, conn):
        cur = conn.cursor()
        sql = "select * from recruitment"
        try:
            cur.execute(sql)
            results = cur.fetchall()
            return results
        except:
            print("获取所有数据")

    def selectByName(self,name,conn):

        cur=conn.cursor()
        sql="select * from recruitment where Job like '%{:}%'".format(name)
        print(sql)
        cur.execute(sql)
        results = cur.fetchall()
        return list(results)
    def AllSelect(self, education, experience,salary,conn):
        cur = conn.cursor()
        exp=''
        edu=''
        print(education,experience,salary)
        if education=='all':
            edu='%%'
        elif education=='junior':
            edu='%初中%'
        elif education=='high':
            edu='%高中%'
        elif education=='college':
            edu='%大专%'
        elif education=='university':
            edu='%本科%'
        elif education=='docter':
            edu='%博士%'
        elif education == 'master':
            edu = '%硕士%'
        elif education=='nothing':
            edu='%不限%'

        if experience=='all':
            exp='%%'
        elif experience=='1-2':
            exp='1-2%'
        elif experience=='1-3':
            exp='1-3%'
        elif experience=='2-5':
            exp='2-5%'
        elif experience == '2-5':
            exp = '2-5%'
        elif experience == '3-4':
            exp = '3-4%'
        elif experience == '5-7':
            exp = '5-7%'
        elif experience == '5-8':
            exp = '5-8%'
        elif experience == '8-12':
            exp = '8-12%'
        elif experience == '12-20':
            exp = '12-20%'
        elif experience == 'over':
            exp = '20%'
        elif experience == 'nolimit':
            exp = "%不限' or Experience like '无需%"

        if salary != 'all' and salary != '9':
            pay = salary.split('-')
            MinPay = pay[0]
            MaxPay = pay[1]
        if salary == '9':
            MinPay = '9'
            MaxPay = '99'


        if salary != 'all':
            sql = "select * from recruitment where Education like '{:}' and Experience like '{:}' and MinPay >= '{:}' and MaxPay <= '{:}'".format(edu,exp,MinPay,MaxPay)
            #sql = "select * from recruitment where education = '{0}' and MinPay >= '{1}' and MaxPay <= '{2}'".format(education, MinPay, MaxPay)
        else:
            sql = sql="select * from recruitment where Education like '{:}' and Experience like '{:}' ".format(edu,exp)
        try:

            cur.execute(sql)
            results = cur.fetchall()
            return list(results)
            """
            print(results)
            for row in results:
                #将每个数据读取出来
                id = row[0]
                Url = row[1]
                Job = row[2]
                MaxPay = row[3]
                MinPay = row[4]
                Education = row[5]
                Experience = row[6]
                City = row[7]
                Company = row[8]
        """
        except:
            print("没有查询到数据")

    #插入数据
    def insert(self,conn, sql):
        cur = conn.cursor()
        lock=threading.Lock()
        try:
            cur.execute(sql)
            conn.commit()
        except():
            # 数据错误回滚
            # db.rollback()
            print("插入错误")
    def analysisJob(self):
        conn = pymysql.connect(host='localhost',
                               user='root',
                               passwd='123456',
                               db='spider',
                               charset="utf8")
        cur = conn.cursor()
        sql = 'select Job,CAST(MaxPay as CHAR) as MaxPay from recruitment order by recruitment.MaxPay desc limit 10'
        try:
            cur.execute(sql)
            results = cur.fetchall()
            results_dict = dict(results)
            conn.close()
        except():
            print('没有查询到数据')
        return results_dict

    def analysisSalary(self):
        conn = pymysql.connect(host='localhost',
                               user='root',
                               passwd='123456',
                               db='spider',
                               charset="utf8")
        cur = conn.cursor()
        sql = 'select Job, CAST(MaxPay as CHAR) as MaxPay, CAST(MinPay as CHAR) as MinPay from recruitment order by recruitment.MaxPay desc limit 10'
        try:
            cur.execute(sql)
            conn.close()
            results = cur.fetchall()
            results_dict = {}
            for i in results:
                max = i[1]
                min = i[2]
                results_list = [max, min]
                results_dict[i[0]] = results_list
        except():
            print('没有查询到数据')
        return results_dict
